package com.logisticsmanagement.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.logisticsmanagement.domain.City;
import com.logisticsmanagement.utils.DataSource;

public class CityDao {
	
	/**
	 * query information of a city by id
	 * @param id
	 * @return
	 */
	public City selectById(Integer id){

		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		City city = null;
		
		try {
			conn= DataSource.getConnection();   //get connection to database
			if(conn == null)
				return null;
			sta = conn.prepareStatement(	 
					"select * from City where cityID=? ");	 //get operation statement object of database 
			sta.setInt(1, id);
			rs = sta.executeQuery();	 //query database, return ResultSet
			if(rs.next()){
				city =  new City();	  
				String name = rs.getString(2);
				Boolean prepareDelete = rs.getBoolean(3);
				Boolean isDelete = rs.getBoolean(4);
				Double asset = rs.getDouble(5);
				city.setCityID(id);
				city.setName(name);
				city.setPrepareDelete(prepareDelete);
				city.setIsDelete(isDelete);
				city.setAsset(asset);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return city;
	}
	
	/**
	 * add city
	 * @param city
	 */
	public Boolean add(City city){
		Connection conn = null;
		PreparedStatement sta = null;
		try {
			conn= DataSource.getConnection();	//get connection to object
			if(conn == null)
				return null;
			sta = conn.prepareStatement(
					"insert into City(cityID,name,prepareDelete,isDelete,"
					+ "asset) values(?,?,?,?,?) ");	 //get operation statement object of database
			sta.setInt(1, city.getCityID());
			sta.setString(2, city.getName());
			sta.setBoolean(3, (city.getPrepareDelete() == true)?true:false);
			sta.setBoolean(4, (city.getIsDelete() == true)?true:false);
			sta.setDouble(5, city.getAsset());
			return (sta.executeUpdate()==1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return false;
	}
	
	/**
	 * update information of city. if update successfully, return true.
	 * @param city
	 * @return
	 */
	public Boolean update(City city){
		Connection conn = null;
		PreparedStatement sta = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return false;
			sta = conn.prepareStatement(
					"update City set name=? and prepareDelete=? and"
					+ " isDelete=? and asset=? where cityID=?");	//get operation statement object of database
			sta.setString(1, city.getName());
			sta.setBoolean(2, (city.getPrepareDelete() == true)?true:false);
			sta.setBoolean(3, (city.getIsDelete() == true)?true:false);
			sta.setDouble(4, city.getAsset());
			sta.setInt(5, city.getCityID());
			sta.executeUpdate();	//execute，update database
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try {	//close connection to database
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return false;
	}
	
	/**
	 * count amount of cities with certain attribute
	 * @param city
	 * @return
	 */
	public Integer selectCount(City city){
		//define sql query statement
		String sql = "select count(*) from City ";	
		boolean iswhere = false;
		int andTag = 0;
		if(city.getCityID() != null) {
			iswhere = true;
		}
		if(city.getName() != null) {
			iswhere = true;
		}
		if(city.getPrepareDelete() != null) {
			iswhere = true;
		}
		if(city.getIsDelete() != null) {
			iswhere = true;
		}
		if(city.getAsset() != null) {
			iswhere = true;
		}
			
		//structure where clause
		if(iswhere){
			sql += "where ";

			if(city.getCityID() != null) {
				sql += ("cityID="+city.getCityID()+" ");
				andTag = 1;
			}
			if(city.getName() != null) {
				if(andTag == 1)
					sql += "and ";
				sql += ("name="+city.getName()+" ");
				andTag = 1;
			}
			if(city.getPrepareDelete() != null) {
				if(andTag == 1)
					sql += "and ";
				sql += ("prepareDelete="+city.getPrepareDelete()+" ");
				andTag = 1;
			}
			if(city.getName() != null) {
				if(andTag == 1)
					sql += "and ";
				sql += ("isDelete="+city.getIsDelete()+" ");
				andTag = 1;
			}
			if(city.getName() != null) {
				if(andTag == 1)
					sql += "and ";
				sql += ("asset="+city.getAsset()+" ");
				andTag = 0;
			}
		}
		
		Connection conn = null;
		Statement sta = null;
		ResultSet rs = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			sta = conn.createStatement();	//get operation statement object of database
			rs = sta.executeQuery(sql);	    //query database, return ResultSet
			if(rs.next()){
				return rs.getInt(1);
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(conn != null){
					try { 	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return null;
	}
	
	/**
	 * the criteria query,the paging query 
	 * @param city
	 * @param nowPage
	 * @param pageSize
	 * @return
	 */
	public List<City> selectPage(City city,int nowPage,int pageSize,String orderBy){
		
		List<City> ret = new ArrayList<City>();
		//define sql query statement
		String sql="select * from City ";	
		boolean iswhere = false;
		int andTag = 0;
		if(city.getCityID() != null) {
			iswhere = true;
		}
		if(city.getName() != null) {
			iswhere = true;
		}
		if(city.getPrepareDelete() != null) {
			iswhere = true;
		}
		if(city.getIsDelete() != null) {
			iswhere = true;
		}
		if(city.getAsset() != null) {
			iswhere = true;
		}
			
		//structure where clause
		if(iswhere){
			sql += "where ";

			if(city.getCityID() != null) {
				sql += ("cityID="+city.getCityID()+" ");
				andTag = 1;
			}
			if(city.getName() != null) {
				if(andTag == 1)
					sql += "and ";
				sql += ("name="+city.getName()+" ");
				andTag = 1;
			}
			if(city.getPrepareDelete() != null) {
				if(andTag == 1)
					sql += "and ";
				sql += ("prepareDelete="+city.getPrepareDelete()+" ");
				andTag = 1;
			}
			if(city.getName() != null) {
				if(andTag == 1)
					sql += "and ";
				sql += ("isDelete="+city.getIsDelete()+" ");
				andTag = 1;
			}
			if(city.getName() != null) {
				if(andTag == 1)
					sql += "and ";
				sql += ("asset="+city.getAsset()+" ");
				andTag = 0;
			}
		}
		
		if(orderBy == null)
			orderBy = "cityID";
		if(nowPage < 1)
			nowPage = 1;
		if(pageSize < 1)
			pageSize = 10;
		
		//structure paging query
		sql += "order by "+orderBy+" asc limite "+(nowPage-1)*pageSize +","+pageSize +";";
		
		Connection conn = null;
		Statement sta = null;
		ResultSet rs = null;
		try {
			conn= DataSource.getConnection();	//get connection to database
			if(conn == null)
				return null;
			sta = conn.createStatement();	//get operation statement object of database
			rs = sta.executeQuery(sql);		//query database, return ResultSet
			//traverse packaged object
			while(rs.next()){
				City retcity =  new City();
				retcity.setCityID(rs.getInt(1));
				retcity.setName(rs.getString(2));
				retcity.setPrepareDelete(rs.getBoolean(3));
				retcity.setIsDelete(rs.getBoolean(4));
				retcity.setAsset(rs.getDouble(5));
				ret.add(retcity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(conn != null){
					try {	//close connection to database
						rs.close();
						sta.close();
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
			}
		}
		return ret;
	}
}


